package stackOverflow;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import stackOverflow.DBhandler.DB.TableNames;


/**
 * @author Boris Cherkasky
 * 
 * Handles all access to the application Data base.<br/>
 * <b>DB tables and fields: </b><br/>
 * <li>{@link DB.TableNames} contains constants for all DB table names
 * <li> {@link DB.Posts} contains constants for all posts table columns 
 * <li> {@link DB.Users} contaings constants for all users table columns<br/>
 * To get the Stirng representation (i.e. table or volumn name) use the value method
 * of the enums (for example: DB.TableNames.POSTS.value())
 * <br/><br/>  
 * <i><b>Note:</b> A prerequisite to the usage of this class is a MySQL database with a prepared schema 
 * called the same as schema class constant. <br/>
 * <b>Setup:</b> Set class constants (i.e. username, password, schema, url and port) to the values deffined in your MySQL DB.<br/> 
 * <b>Correct Usage:</b> DBhandler will create ONE connection to the DB on class instanciation. as long as the class lives, 
 * the connection lives, so when querying the DB more than once - use the same DBhandle instance.</i>
 */
public class DBhandler {
    
    //DB configuration
    private final static String username = "root";
    private final static String password = "pwd";
    private final static String schema	 = "stackoverflow";
    private final static String url	 = "localhost";
    private final static String port	 = "3306";
    
    //an instance of the DB connection object
    private Connection con;
    //when accumulating batches, save the statement containing batches
    private Statement sttmnt;
    //number of element in each DB commit (on write and update)
    private static final int elementsPerWrite = 50;
    //number of element in the batch
    private int batchSize;
    
    //DB table constants
    public static class DB {
	
	/**
	 * Properties and statements used in creating the DB
	 */
	private enum Properties{
	    VARCHAR		(" VARCHAR(50) NOT NULL ,"),
	    INT			(" INT NOT NULL ,"),
	    BOOL		(" TINYINT(1) NOT NULL ,"),
	    TEXT		(" TEXT NOT NULL ,"),
	    
	    CREATE 		("CREATE TABLE IF NOT EXISTS " + schema + "."),
	    INSERT		("INSERT INTO " + schema + "." ),
	    USE_SCHEMA		("use " + schema),
	    SELECT_POSTS	("SELECT * FROM posts AS p JOIN users AS u ON u." + 
		    			DB.Users.ID.value() + "=p." + DB.Posts.USER_ID.value()),
	    UPDATE_POSTS	("UPDATE " + DB.TableNames.POSTS.value() + " SET "),
	    DROP 		("DROP TABLE IF EXISTS "),
	    DELETE		("DELETE FROM ");
	    
	    private final String query;
	    private Properties(String _query) {query = _query;}
	    public String value(){return query;}
	}
	
	/**
	 * names of db tables
	 */
	public enum TableNames{
	    //Final application tables
	    POSTS		("posts"),
	    USERS		("users");
	    
	    private final String name;
	    private TableNames(String _name) {name = _name;}
	    public String value(){return name;}
	}

	/**
	 * Fields of posts db table
	 */
	public enum Posts{
	    ID			("id"),
	    IS_QUESTION		("is_question"),
	    IS_ACCEPTED		("is_accepted"),
	    CREATION_DATE	("creation_date"),
	    LAST_ACTIVITY	("last_activity"),
	    BODY		("body"),
	    SCORE		("score"),
	    VIEWS		("view_count"),
	    USER_ID		("author_id"),
	    TITLE		("title"),
	    UP_VOTES		("up_votes"),	//will hold *sum* of up votes
	    DOWN_VOTES		("down_votes"); //will hold *sum* of down votes
	    
	    private final String field;
	    Posts(String _field){field = _field;}
	    public String value(){return field;}
	}
	
	/**
	 * Fields of user DB table
	 */
	public enum Users{
	    ID			("id"),
	    REPUTATION		("reputation"),
	    DISPLAY_NAME	("display_name");
	    
	    private final String field;
	    Users(String _field){field = _field;}
	    public String value(){return field;}
	}
    }
    
    /**
     * On creation - create a fresh DB connection
     * 
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public DBhandler() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
	createConnection();
    }
    
    /**
     * Close DB connection to the 
     */
    @Override
    protected void finalize() throws Throwable {
        super.finalize();
        sttmnt.close();
        con.close();
    }
    
    /**
     * Create a connection to the Database and set private class property {con} to reference to that connection.
     * Also create a fresh statement.
     * 
     * @return connection
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public Connection createConnection() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException{
	Class.forName ("com.mysql.jdbc.Driver");//.newInstance ();
	String location = "jdbc:mysql://" + url + ":" + port;
        con = DriverManager.getConnection(location,username,password);
        //set schema
        sttmnt = con.createStatement();
        sttmnt.addBatch(DB.Properties.USE_SCHEMA.value());
        sttmnt.executeBatch();
        sttmnt.close();
        sttmnt = con.createStatement();
        
        return con;
    }
    
    /**
     * Create DB used for this application
     * @throws SQLException 
     */
    public void createTables() throws SQLException{
	//users table Create statement 
	String users	= DB.Properties.CREATE.value()	+ DB.TableNames.USERS.value() + " (" + 
		DB.Users.ID.value()			+ DB.Properties.INT.value() 	+
		DB.Users.DISPLAY_NAME.value()		+ DB.Properties.VARCHAR.value() 	+
		DB.Users.REPUTATION.value() 		+ DB.Properties.INT.value() 	+
		"PRIMARY KEY ("+ DB.Users.ID.value() +") )";
	//posts table Create statement 
	String posts	= DB.Properties.CREATE.value()	+ DB.TableNames.POSTS.value()	+ " (" + 
		DB.Posts.ID.value()			+ DB.Properties.INT.value()	+ 
		DB.Posts.CREATION_DATE.value() 		+ DB.Properties.VARCHAR.value()	+
		DB.Posts.DOWN_VOTES.value()		+ DB.Properties.INT.value()	+
		DB.Posts.IS_ACCEPTED.value()		+ DB.Properties.BOOL.value()	+
		DB.Posts.IS_QUESTION.value()		+ DB.Properties.BOOL.value()	+
		DB.Posts.LAST_ACTIVITY.value()		+ DB.Properties.VARCHAR.value()	+
		DB.Posts.UP_VOTES.value()		+ DB.Properties.INT.value()	+
		DB.Posts.SCORE.value()			+ DB.Properties.INT.value()	+
		DB.Posts.VIEWS.value()			+ DB.Properties.INT.value()	+
		DB.Posts.BODY.value()			+ DB.Properties.TEXT.value()	+
		DB.Posts.USER_ID.value()		+ DB.Properties.INT.value()	+
		DB.Posts.TITLE.value()			+ DB.Properties.TEXT.value()	+
		"PRIMARY KEY (" + DB.Posts.ID.value() + ") ) ";

	
	//Create JDBC SQL statement 
	Statement sttmnt = con.createStatement();
	sttmnt.addBatch(users);
	sttmnt.addBatch(posts);

	//execute create operation
	sttmnt.executeBatch();
	sttmnt.close();
    }
    
    /**
     * Drop a single table
     * 
     * @param table
     * @param returnQuery - if set, the drop query will be returned and the table <b>will not be dropped!</b>
     * @return
     * @throws SQLException 
     */
    public String dropTable(DB.TableNames table, boolean returnQuery) throws SQLException{
	String drop = DB.Properties.DROP.value() + schema + "." + table.value();
	if (returnQuery){
	    return drop;
	}
	//drop table
	Statement sttmnt = con.createStatement();
	sttmnt.addBatch(drop);
	sttmnt.executeBatch();
	sttmnt.close();
	return null;
    }
    
    /**
     * Delete all content from a table
     * 
     * @param table
     * @throws SQLException
     */
    public void clearTable(DB.TableNames table) throws SQLException{
	String query = DB.Properties.DELETE.value() + table.value();
	Statement sttmnt = con.createStatement();
	sttmnt.addBatch(query);
	sttmnt.executeBatch();
	sttmnt.close();
    }
    
    /**
     * Drop tables from the DB. <br/>
     * if dropOnlyTmp flag set - only temporary tables will be dropped.
     * 
     * @throws SQLException
     */
    public void dropDB() throws SQLException{
	//prepare drop statements
	Statement sttmnt = con.createStatement();
	//when dropping all tables (and not only temporary ones)
	sttmnt.addBatch(dropTable(DB.TableNames.POSTS, true));
	sttmnt.addBatch(dropTable(DB.TableNames.USERS, true));
    }
    
    /**
     * Add a post to the DB
     * @param creationDate
     * @param lastAcctivity
     * @param title
     * @param postID
     * @param score
     * @param viewCount
     * @param isAccepted
     * @param isQuestion
     * @param body
     * @param upVotes
     * @param downVotes
     * @param authorID
     * 
     * @throws SQLException
     */
    public void addPost(String creationDate, String lastAcctivity, String title, int postID,
	    int score, int viewCount, boolean isAccepted, boolean isQuestion, String body, 
	    int upVotes, int downVotes, int authorID) throws SQLException{
	
	String conlumns = DB.Posts.CREATION_DATE.value() 	+ ", " +
		DB.Posts.DOWN_VOTES.value()	 		+ ", " +
		DB.Posts.ID.value() 				+ ", " +
		DB.Posts.IS_ACCEPTED.value() 			+ ", " +
		DB.Posts.IS_QUESTION.value() 			+ ", " +
		DB.Posts.LAST_ACTIVITY.value()		 	+ ", " +
		DB.Posts.UP_VOTES.value()			+ ", " +
		DB.Posts.USER_ID.value()			+ ", " +
		DB.Posts.BODY.value()				+ ", " +
		DB.Posts.SCORE.value()				+ ", " +
		DB.Posts.VIEWS.value()				+ ", " +
		DB.Posts.TITLE.value();
	
	String values	= "'" + creationDate + "', " + downVotes + ", " + postID + ", " + isAccepted + ", " + 
		isQuestion + ", '" + lastAcctivity + "', " + upVotes + ", " + authorID + ", " + "?" + ", " + 
		score + ", " + viewCount + ", " + "?" ;
	
	String query 	= DB.Properties.INSERT.value() + DB.TableNames.POSTS.value() + 
		" ( " + conlumns + " ) " + " VALUES ( " + values + " )";
	
	//create SQL statement and execute it
	PreparedStatement sttmnt = con.prepareStatement(query);
	sttmnt.setString(1, body);
	sttmnt.setString(2, title);
	sttmnt.addBatch();
	sttmnt.executeBatch();
	sttmnt.close();
    }

    /**
     * Add a batch to sttmnt attribute - if there are elementsPerWrite element in the statement - execute it
     * 
     * @param sql
     * @throws SQLException
     */
    private void addBatch(String sql) throws SQLException{
	sttmnt.addBatch(sql);
	batchSize++;
	if (batchSize == elementsPerWrite){
	    executeBatches();
	}
    }
    
    /**
     * Execute batches accumulated in sttmnt attribute
     * @throws SQLException
     */
    public void executeBatches() throws SQLException {
	if (batchSize == 0){
	    //Nothing wating for commit
	    return;
	}
	sttmnt.executeBatch();
	sttmnt.close();
	sttmnt		= con.createStatement();
	batchSize	= 0;
    }
    
    /**
     * Query DB posts.<br/>
     * Posts table referenced as "p".<br/>
     * Users table referenced as "u".<br/>
     * 
     * @param where - the content of a WHERE statement (i.e. without the "WHERE" keyword).<br/>
     * The statement can contain any condition on any field of the users and posts talbe 
     * (the interesting ones are title and body).<br/>
     * To fetch ALL entries, set where parm to be null.<br/>
     * <i><b>Note:</b> I strongly recomend to work with the constants defined in {@link DB.Posts} and {@link DB.Users}
     * when writing querys to this method.</i>
     * @return
     * @throws SQLException
     */
    public ArrayList<CodeObject> queryPosts(String where) throws SQLException{
	String query;
	if (where == null){
	    //fetch all entries.
	    query = DB.Properties.SELECT_POSTS.value();
	} else {
	    query = DB.Properties.SELECT_POSTS.value() + " WHERE " + where;
	}
	 
	//execute query
	Statement sttmnt = con.createStatement();
	ResultSet rs = sttmnt.executeQuery(query);	
	if (isEmptyResultSet(rs)){
	    //nothing found
	    return new ArrayList<CodeObject>();
	}
	ArrayList<CodeObject> results = rsToPosts(rs);
	rs.close();
	sttmnt.close();
	return results;
    }
    
    /**
     * Extract all Posts from result set - Build a code object from each found row <br/>
     * Each code obecjt's code attribute will contain an ArrayList with 1 element - WHOLE post body.
     * 
     * @param rs
     * @return
     * @throws SQLException
     */
    private ArrayList<CodeObject> rsToPosts(ResultSet rs) throws SQLException {
	ArrayList<CodeObject> results = new ArrayList<CodeObject>();
	while (rs.next()){
	    CodeObject co;
	    boolean isQuestion = rs.getBoolean(DB.Posts.IS_QUESTION.value());
	    if (isQuestion){
		//create code object
		co = new CodeObject();
	    } else{
		//create answer object
		co = new AnswerObject();
		//set answer unique fields
		((AnswerObject) co).setAccepted(rs.getBoolean(DB.Posts.IS_ACCEPTED.value()));
	    }
	    //get all other data from the result set
	    co.setCode(rs.getString(DB.Posts.BODY.value()));
	    co.setDownVoteCount(rs.getInt(DB.Posts.DOWN_VOTES.value()));
	    co.setId(rs.getInt(DB.Posts.ID.value()));
	    co.setScore(rs.getInt(DB.Posts.SCORE.value()));
	    co.setUpVoteCount(rs.getInt(DB.Posts.UP_VOTES.value()));
	    co.setUserDisplayName(rs.getString(DB.Users.DISPLAY_NAME.value()));
	    co.setUserRep(rs.getInt(DB.Users.REPUTATION.value()));
	    co.setViewCount(rs.getInt(DB.Posts.VIEWS.value()));
	    co.setLastActivityDateString(rs.getString(DB.Posts.LAST_ACTIVITY.value()));
	    co.setCreationDateString(rs.getString(DB.Posts.CREATION_DATE.value()));
	    //add code object to results
	    results.add(co);
	}
	return results;
    }

    /**
     * Add a user to the DB
     * @param userID
     * @param dispName
     * @param rep
     * @throws SQLException
     */
    public void addUser(int userID, String dispName, int rep) throws SQLException{
	String conlumns = DB.Users.DISPLAY_NAME.value() 		+ ", " +
		DB.Users.ID.value()					+ ", " +
		DB.Users.REPUTATION.value();
	
	String values = "? , " + userID + ", " + rep;
	
	String query 	= DB.Properties.INSERT.value() + DB.TableNames.USERS.value() + 
	" ( " + conlumns + " ) " + " VALUES ( " + values + " )";

	//create SQL statement and execute it
	PreparedStatement p = con.prepareStatement(query);
	p.setString(1, dispName);
	p.execute();
	p.close();
//	addBatch(query, false);
    }
    
    
    /**
     * check if the Resultset is empty
     * 
     * @param rs
     * @return boolean - true iff the result set is empty
     * @throws SQLException 
     */
    public boolean isEmptyResultSet(ResultSet rs) throws SQLException{
	if (!rs.first()){
	    rs.beforeFirst();
	    return true;
	}
	rs.beforeFirst();    
	return false;
    }
        
    /**
     * Get all known users - all users that has posts in the DB
     * @param id
     * @param table
     * 
     * @return
     * @throws SQLException
     */
    public ArrayList<Integer> fetchIDs(String id, TableNames table) throws SQLException{
	String query = "SELECT "+ id + " FROM " + table.value() + " GROUP BY " + id;  
	Statement sttmnt = con.createStatement();
	ResultSet rs = sttmnt.executeQuery(query);
	ArrayList<Integer> results = new ArrayList<Integer>();
	//get all userIDs from ResultSet
	while (rs.next()){
	    int uid = rs.getInt(id);
	    results.add(uid);
	}
	rs.close();
	sttmnt.close();
	return results;
    }
    
    /**
     * Update posts table - add up and down votes sum to post entry with postID
     * @param postID
     * @param up
     * @param down
     * 
     * @throws SQLException
     */
    public void addVotes(int postID, int up, int down) throws SQLException{
	String query = DB.Properties.UPDATE_POSTS.value() 	+ 
		DB.Posts.UP_VOTES.value() 	+ "=" + up 	+ ", " +
		DB.Posts.DOWN_VOTES.value()	+ "=" + down	+
		" WHERE " + DB.Posts.ID.value()	+ "=" + postID;
	
	addBatch(query);
    }
    
    /**
     * Set post to be an accepted answer
     * 
     * @param postID
     * @throws SQLException
     */
    public void setAcceptedAnswer(int postID) throws SQLException{
	String query = DB.Properties.UPDATE_POSTS.value() 	+ 
        	DB.Posts.IS_ACCEPTED.value()	+ "=" + true	+
        	" WHERE " + DB.Posts.ID.value()	+ "=" + postID;

	addBatch(query);
    }
    
    /**
     * Return the title of a post
     * 
     * @param postID
     * @return
     * @throws SQLException
     */
    public String getPostTitle(int postID) throws SQLException{
	String query = "SELECT " + DB.Posts.TITLE.value() + " FROM " + DB.TableNames.POSTS.value() + 
		" WHERE " + DB.Posts.ID.value() + "=" + postID;
	Statement sttmnt = con.createStatement();
	ResultSet rs = sttmnt.executeQuery(query);
	if (isEmptyResultSet(rs)){
	    rs.close();
	    sttmnt.close();
	    return null;
	}
	rs.next();
	String title = rs.getString(DB.Posts.TITLE.value());
	rs.close();
	sttmnt.close();
	return title;
    }

    /**
     * Update post's title
     * 
     * @param title
     * @param postID
     * @throws SQLException
     */
    public void updatePostTitle(String title, int postID) throws SQLException {
	String query = DB.Properties.UPDATE_POSTS.value() + DB.Posts.TITLE.value() + "= ? WHERE " 
		+ DB.Posts.ID.value() + "=" + postID;
	PreparedStatement sttmnt = con.prepareStatement(query);
	sttmnt.setString(1, title);
	sttmnt.execute();
	sttmnt.close();
    }
}
